package cn.iocoder.yudao.module.wengfu.framework.excel.core.util.wengfu;

import cn.iocoder.yudao.module.wengfu.controller.admin.consignmentprocessingreport.vo.ConsignmentProcessingReportSaveReqVO;
import cn.iocoder.yudao.module.wengfu.service.consignmentprocessingreport.ConsignmentProcessingReportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 报表数据处理服务 - 专门处理报表数据计算和保存，不涉及Excel导出
 * 基于内存计算，不依赖POI，完整复刻PhosphorusOreSheetWriteHandler的业务逻辑
 */
@Slf4j
@Service
public class ReportDataProcessService {

    @Autowired
    private ConsignmentProcessingReportService consignmentProcessingReportService;

    /** 存储所有单元格的计算结果 */
    private final Map<String, Object> cellValues = new ConcurrentHashMap<>();

    /** 记录所有公式及其所在单元格地址 */
    private final Map<String, String> formulaMap = new ConcurrentHashMap<>();

    /** 动态数据（来自 Service） */
    private Map<String, BigDecimal> data;

    /**
     * 处理并保存报表数据（核心业务方法）
     * 不生成Excel文件，专注于数据计算和持久化
     */
    public void processAndSaveReportData() {
        try {
            log.info("开始执行报表数据处理逻辑...");

            // 1. 清理历史数据
            cellValues.clear();
            formulaMap.clear();

            // 2. 获取基础数据
            data = consignmentProcessingReportService.UpdateConsignmentProcessingReport();
            log.info("从服务获取数据，数据大小: {}", data != null ? data.size() : 0);

            // 3. 设置所有基础数据
            setupAllBaseData();

            // 4. 设置所有公式（完整复刻PhosphorusOreSheetWriteHandler的公式逻辑）
            setupAllFormulas();

            // 5. 计算所有公式
            calculateAllFormulas();

            // 6. 执行数据保存逻辑
            //先删除历史数据
            saveProcessedData();

            log.info("报表数据处理逻辑执行完成，共处理 {} 个数据项，{} 个公式",
                    cellValues.size(), formulaMap.size(),data);

        } catch (Exception e) {
            log.error("报表数据处理失败", e);
            throw new RuntimeException("数据处理失败", e);
        }
    }

    /**
     * 设置所有基础数据到内存中
     */
    private void setupAllBaseData() {
        log.info("开始设置基础数据...");

        // === 受托加工磷矿石 ===
        setupFirstCategoryData();
        setupHuanglinCompanyData();
        setupALayerData();
        setupALayerReturnPowderData();
        setupBLayerData();
        setupAssignedBLayerData();

        // === 购进磷矿石 ===
        setupPurchaseOreData();
        setupSelfProducedOreData();
        setupExternalOreData();

        // === 双山坪公司 ===
        setupShuangshanpingData();
        setupShuangshanpingSelfProducedData();
        setupShuangshanpingExternalOreData();

        // === 有福公司 ===
        setupYoufuCompanyData();
        setupYoufuSelfProducedData();
        setupYoufuExternalOreData();

        log.info("基础数据设置完成");
    }

    /**
     * 设置所有公式 - 完整复刻PhosphorusOreSheetWriteHandler的公式逻辑
     */
    private void setupAllFormulas() {
        log.info("开始设置公式...");

        // === 购进磷矿石公式 ===
        setupPurchaseOreFormulas();
        setupSelfProducedOreFormulas();
        setupExternalOreFormulas();

        // === 双山坪公司公式 ===
        setupShuangshanpingFormulas();
        setupShuangshanpingSelfProducedFormulas();
        setupShuangshanpingExternalOreFormulas();

        // === 有福公司公式 ===
        setupYoufuCompanyFormulas();
        setupYoufuSelfProducedFormulas();
        setupYoufuExternalOreFormulas();

        log.info("公式设置完成，共 {} 个公式", formulaMap.size());
    }

    // ==================== 数据设置方法 ====================

    private void setupFirstCategoryData() {
        // 一、受托加工磷矿石
        setCellValue("D5", getValueFromData("D8"));
        setCellValue("E5", getValueFromData("E8"));
        setCellValue("D6", getValueFromData("D9"));
        setCellValue("D7", getValueFromData("D10"));
    }

    private void setupHuanglinCompanyData() {
        // 1 黄磷公司
        setCellValue("D8", getValueFromData("F7"));
        setCellValue("E8", getValueFromData("C7"));
        setCellValue("D9", getValueFromData("H7"));
        setCellValue("D10", getValueFromData("I7"));
    }

    private void setupALayerData() {
        // 1.1 a层矿
        setCellValue("D11", getValueFromData("F8"));
        setCellValue("E11", getValueFromData("C8"));
        setCellValue("D12", getValueFromData("H8"));
        setCellValue("D13", getValueFromData("I8"));
    }

    private void setupALayerReturnPowderData() {
        // 其中a层矿返粉
        setCellValue("D14", getValueFromData("F19"));
        setCellValue("E14", getValueFromData("C19"));
        setCellValue("D15", getValueFromData("H19"));
        setCellValue("D16", getValueFromData("I19"));
    }

    private void setupBLayerData() {
        // 1.2 b层矿
        setCellValue("D17", getValueFromData("F9"));
        setCellValue("E17", getValueFromData("C9"));
        setCellValue("D18", getValueFromData("H9"));
        setCellValue("D19", getValueFromData("I9"));
    }

    private void setupAssignedBLayerData() {
        // 其中划拨b层矿
        setCellValue("D20", getValueFromData("AA7"));
        setCellValue("E20", getValueFromData("Z7"));
        setCellValue("D21", getValueFromData("AC7"));
        setCellValue("D22", getValueFromData("AD7"));
    }

    private void setupPurchaseOreData() {
        // 二、购进磷矿石 - 基础数据
        setCellValue("D23", getValueFromData("D23"));
        setCellValue("E23", getValueFromData("E23"));
        setCellValue("F23", getValueFromData("F23"));
        setCellValue("G23", getValueFromData("G23"));
        setCellValue("H23", getValueFromData("H23"));
        setCellValue("I23", getValueFromData("I23"));
        setCellValue("K23", getValueFromData("K23"));
        setCellValue("L23", getValueFromData("L23"));

        // MgO(%) 子行
        setCellValue("D24", getValueFromData("D24"));
        setCellValue("F24", getValueFromData("F24"));
        setCellValue("G24", getValueFromData("G24"));
        setCellValue("H24", getValueFromData("H24"));
        setCellValue("K24", getValueFromData("K24"));

        // R2O3(%) 子行
        setCellValue("D25", getValueFromData("D25"));
        setCellValue("F25", getValueFromData("F25"));
        setCellValue("G25", getValueFromData("G25"));
        setCellValue("H25", getValueFromData("H25"));
        setCellValue("K25", getValueFromData("K25"));

        // SiO2(%) 子行
        setCellValue("D26", getValueFromData("D26"));
        setCellValue("F26", getValueFromData("F26"));
        setCellValue("G26", getValueFromData("G26"));
        setCellValue("H26", getValueFromData("H26"));
        setCellValue("K26", getValueFromData("K26"));
    }

    private void setupSelfProducedOreData() {
        // 1 自产矿石 - 基础数据
        setCellValue("D27", getValueFromData("D27"));
        setCellValue("E27", getValueFromData("E27"));
        setCellValue("F27", getValueFromData("F27"));
        setCellValue("G27", getValueFromData("G27"));
        setCellValue("H27", getValueFromData("H27"));
        setCellValue("I27", getValueFromData("I27"));
        setCellValue("K27", getValueFromData("K27"));
        setCellValue("L27", getValueFromData("L27"));

        // MgO(%) 子行
        setCellValue("D28", getValueFromData("D28"));
        setCellValue("F28", getValueFromData("F28"));
        setCellValue("G28", getValueFromData("G28"));
        setCellValue("H28", getValueFromData("H28"));
        setCellValue("K28", getValueFromData("K28"));

        // R2O3(%) 子行
        setCellValue("D29", getValueFromData("D29"));
        setCellValue("F29", getValueFromData("F29"));
        setCellValue("G29", getValueFromData("G29"));
        setCellValue("H29", getValueFromData("H29"));
        setCellValue("K29", getValueFromData("K29"));

        // SiO2(%) 子行
        setCellValue("D30", getValueFromData("D30"));
        setCellValue("F30", getValueFromData("F30"));
        setCellValue("G30", getValueFromData("G30"));
        setCellValue("H30", getValueFromData("H30"));
        setCellValue("K30", getValueFromData("K30"));
    }


    private void setupExternalOreData() {
        // 2 外部矿石利用 - 基础数据
        setCellValue("D31", getValueFromData("D31"));
        setCellValue("E31", getValueFromData("E31"));
        setCellValue("F31", getValueFromData("F31"));
        setCellValue("G31", getValueFromData("G31"));
        setCellValue("H31", getValueFromData("H31"));
        setCellValue("I31", getValueFromData("I31"));
        setCellValue("K31", getValueFromData("K31"));
        setCellValue("L31", getValueFromData("L31"));

        // MgO(%) 子行
        setCellValue("D32", getValueFromData("D32"));
        setCellValue("F32", getValueFromData("F32"));
        setCellValue("G32", getValueFromData("G32"));
        setCellValue("H32", getValueFromData("H32"));
        setCellValue("K32", getValueFromData("K32"));

        // R2O3(%) 子行
        setCellValue("D33", getValueFromData("D33"));
        setCellValue("F33", getValueFromData("F33"));
        setCellValue("G33", getValueFromData("G33"));
        setCellValue("H33", getValueFromData("H33"));
        setCellValue("K33", getValueFromData("K33"));

        // SiO2(%) 子行
        setCellValue("D34", getValueFromData("D34"));
        setCellValue("F34", getValueFromData("F34"));
        setCellValue("G34", getValueFromData("G34"));
        setCellValue("H34", getValueFromData("H34"));
        setCellValue("K34", getValueFromData("K34"));
    }

    private void setupShuangshanpingData() {
        // 1 双山坪公司 - 基础数据
        setCellValue("D35", getValueFromData("D35"));
        setCellValue("E35", getValueFromData("E35"));
        setCellValue("F35", getValueFromData("F35"));
        setCellValue("G35", getValueFromData("G35"));
        setCellValue("H35", getValueFromData("H35"));
        setCellValue("I35", getValueFromData("I35"));
        setCellValue("K35", getValueFromData("K35"));
        setCellValue("L35", getValueFromData("L35"));

        // MgO(%) 子行
        setCellValue("D36", getValueFromData("D36"));
        setCellValue("F36", getValueFromData("F36"));
        setCellValue("G36", getValueFromData("G36"));
        setCellValue("H36", getValueFromData("H36"));
        setCellValue("K36", getValueFromData("K36"));

        // R2O3(%) 子行
        setCellValue("D37", getValueFromData("D37"));
        setCellValue("F37", getValueFromData("F37"));
        setCellValue("G37", getValueFromData("G37"));
        setCellValue("H37", getValueFromData("H37"));
        setCellValue("K37", getValueFromData("K37"));

        // SiO2(%) 子行
        setCellValue("D38", getValueFromData("D38"));
        setCellValue("F38", getValueFromData("F38"));
        setCellValue("G38", getValueFromData("G38"));
        setCellValue("H38", getValueFromData("H38"));
        setCellValue("K38", getValueFromData("K38"));
    }

    private void setupYoufuCompanyData() {
        // 2 有福公司 - 基础数据
        setCellValue("D47", getValueFromData("D47"));
        setCellValue("E47", getValueFromData("E47"));
        setCellValue("F47", getValueFromData("F47"));
        setCellValue("G47", getValueFromData("G47"));
        setCellValue("H47", getValueFromData("H47"));
        setCellValue("I47", getValueFromData("I47"));
        setCellValue("K47", getValueFromData("K47"));
        setCellValue("L47", getValueFromData("L47"));

        // MgO(%) 子行
        setCellValue("D48", getValueFromData("D48"));
        setCellValue("F48", getValueFromData("F48"));
        setCellValue("G48", getValueFromData("G48"));
        setCellValue("H48", getValueFromData("H48"));
        setCellValue("K48", getValueFromData("K48"));

        // R2O3(%) 子行
        setCellValue("D49", getValueFromData("D49"));
        setCellValue("F49", getValueFromData("F49"));
        setCellValue("G49", getValueFromData("G49"));
        setCellValue("H49", getValueFromData("H49"));
        setCellValue("K49", getValueFromData("K49"));

        // SiO2(%) 子行
        setCellValue("D50", getValueFromData("D50"));
        setCellValue("F50", getValueFromData("F50"));
        setCellValue("G50", getValueFromData("G50"));
        setCellValue("H50", getValueFromData("H50"));
        setCellValue("K50", getValueFromData("K50"));
    }

    private void setupShuangshanpingSelfProducedData() {
        // 1.1 自产矿石（双山坪公司）
        setCellValue("D39", getValueFromData("C24"));
        setCellValue("E39", getValueFromData("B24"));
        setCellValue("F39", getValueFromData("F39"));
        setCellValue("D40", getValueFromData("D24"));
        setCellValue("F40", getValueFromData("F40"));
        setCellValue("D41", getValueFromData("E24"));
        setCellValue("F41", getValueFromData("F41"));
        setCellValue("D42", getValueFromData("F24"));
        setCellValue("F42", getValueFromData("F42"));
        setCellValue("H39", getValueFromData("H39"));
        setCellValue("I39", getValueFromData("I39"));
        setCellValue("H40", getValueFromData("H40"));
        setCellValue("H41", getValueFromData("H41"));
        setCellValue("H42", getValueFromData("H42"));
        setCellValue("K39", getValueFromData("K39"));
        setCellValue("L39", getValueFromData("L39"));
        setCellValue("K40", getValueFromData("K40"));
        setCellValue("K41", getValueFromData("K41"));
        setCellValue("K42", getValueFromData("K42"));
    }

    private void setupShuangshanpingExternalOreData() {
        // 1.2 外部矿石利用（双山坪公司）
        setCellValue("D43", getValueFromData("AS27"));
        setCellValue("E43", getValueFromData("AR27"));
        setCellValue("F43", getValueFromData("F43"));
        setCellValue("D44", getValueFromData("AT27"));
        setCellValue("F44", getValueFromData("F44"));
        setCellValue("D45", getValueFromData("AU27"));
        setCellValue("F45", getValueFromData("F45"));
        setCellValue("D46", getValueFromData("AV27"));
        setCellValue("F46", getValueFromData("F46"));
        setCellValue("H43", getValueFromData("H43"));
        setCellValue("I43", getValueFromData("I43"));
        setCellValue("H44", getValueFromData("H44"));
        setCellValue("H45", getValueFromData("H45"));
        setCellValue("H46", getValueFromData("H46"));
        setCellValue("K43", getValueFromData("K43"));
        setCellValue("L43", getValueFromData("L43"));
        setCellValue("K44", getValueFromData("K44"));
        setCellValue("K45", getValueFromData("K45"));
        setCellValue("K46", getValueFromData("K46"));
    }

    private void setupYoufuSelfProducedData() {
        // 2.1 自产矿石（有福公司）
        setCellValue("D51", getValueFromData("BW25"));
        setCellValue("E51", getValueFromData("BV25"));
        setCellValue("F51", getValueFromData("F51"));
        setCellValue("D52", getValueFromData("BX25"));
        setCellValue("F52", getValueFromData("F52"));
        setCellValue("D53", getValueFromData("BY25"));
        setCellValue("F53", getValueFromData("F53"));
        setCellValue("D54", getValueFromData("BZ25"));
        setCellValue("F54", getValueFromData("F54"));
        setCellValue("H51", getValueFromData("H51"));
        setCellValue("I51", getValueFromData("I51"));
        setCellValue("H52", getValueFromData("H52"));
        setCellValue("H53", getValueFromData("H53"));
        setCellValue("H54", getValueFromData("H54"));
        setCellValue("K51", getValueFromData("K51"));
        setCellValue("L51", getValueFromData("L51"));
        setCellValue("K52", getValueFromData("K52"));
        setCellValue("K53", getValueFromData("K53"));
        setCellValue("K54", getValueFromData("K54"));
    }

    private void setupYoufuExternalOreData() {
        // 2.2 外部矿石利用（有福公司）
        setCellValue("D55", getValueFromData("AS26"));
        setCellValue("E55", getValueFromData("AR26"));
        setCellValue("F55", getValueFromData("F55"));
        setCellValue("D56", getValueFromData("AT26"));
        setCellValue("F56", getValueFromData("F56"));
        setCellValue("D57", getValueFromData("AU26"));
        setCellValue("F57", getValueFromData("F57"));
        setCellValue("D58", getValueFromData("AV26"));
        setCellValue("F58", getValueFromData("F58"));
        setCellValue("H55", getValueFromData("H55"));
        setCellValue("I55", getValueFromData("I55"));
        setCellValue("H56", getValueFromData("H56"));
        setCellValue("H57", getValueFromData("H57"));
        setCellValue("H58", getValueFromData("H58"));
        setCellValue("K55", getValueFromData("K55"));
        setCellValue("L55", getValueFromData("L55"));
        setCellValue("K56", getValueFromData("K56"));
        setCellValue("K57", getValueFromData("K57"));
        setCellValue("K58", getValueFromData("K58"));
    }

    // ==================== 完整的公式设置方法 ====================

    private void setupPurchaseOreFormulas() {
        // 二、购进磷矿石 - 完整公式
        setFormula("D23", "IF(ISERROR((D27*E27+D31*E31)/E23),0,((D27*E27+D31*E31)/E23))");
        setFormula("E23", "E27+E31");
        setFormula("F23", "(E23*D23+K23*L23)/G23"); // 补充 F23
        setFormula("G23", "(E23*D23+K23*L23)/G23");
        setFormula("H23", "IF(ISERROR((H27*I27+H31*I31)/I23),0,((H27*I27+H31*I31)/I23))");
        setFormula("I23", "I27+I31");
        setFormula("K23", "IF(ISERROR((K27*L27+K31*L31)/L23),0,((K27*L27+K31*L31)/L23))");
        setFormula("L23", "L27+L31");

        // MgO(%) 子行
        setFormula("D24", "IF(ISERROR((D28*E27+D32*E31)/E23),0,((D28*E27+D32*E31)/E23))");
        setFormula("F24", "(E23*D24+K24*L23)/G23"); // 补充 F24
        setFormula("G24", "(E23*D24+K24*L23)/G23");
        setFormula("H24", "IF(ISERROR((H28*I27+H32*I31)/I23),0,((H28*I27+H32*I31)/I23))");
        setFormula("K24", "IF(ISERROR((K28*L27+K32*L31)/L23),0,((K28*L27+K32*L31)/L23))");

        // R2O3(%) 子行
        setFormula("D25", "IF(ISERROR((D29*E27+D33*E31)/E23),0,((D29*E27+D33*E31)/E23))");
        setFormula("F25", "(E23*D25+K25*L23)/G23"); // 补充 F25
        setFormula("G25", "(E23*D25+K25*L23)/G23");
        setFormula("H25", "IF(ISERROR((H29*I27+H33*I31)/I23),0,((H29*I27+H33*I31)/I23))");
        setFormula("K25", "IF(ISERROR((K29*L27+K33*L31)/L23),0,((K29*L27+K33*L31)/L23))");

        // SiO2(%) 子行
        setFormula("D26", "IF(ISERROR((D30*E27+D34*E31)/E23),0,((D30*E27+D34*E31)/E23))");
        setFormula("F26", "(E23*D26+K26*L23)/G23"); // 补充 F26
        setFormula("G26", "(E23*D26+K26*L23)/G23");
        setFormula("H26", "IF(ISERROR((H30*I27+H34*I31)/I23),0,((H30*I27+H34*I31)/I23))");
        setFormula("K26", "IF(ISERROR((K30*L27+K34*L31)/L23),0,((K30*L27+K34*L31)/L23))");
    }

    private void setupSelfProducedOreFormulas() {
        // 1 自产矿石 - 完整公式
        setFormula("D27", "IF(ISERROR((D39*E39+E51*D51)/E27),0,((D39*E39+E51*D51)/E27))");
        setFormula("E27", "E39+E51");
        setFormula("F27", "IF(ISERROR((E27*D27+K27*L27)/G27),0,(E27*D27+K27*L27)/G27)"); // 补充 F27
        setFormula("G27", "IF(ISERROR((E27*D27+K27*L27)/G27),0,(E27*D27+K27*L27)/G27)");
        setFormula("H27", "IF(ISERROR((H39*I39+I51*H51)/I27),0,((H39*I39+I51*H51)/I27))");
        setFormula("I27", "I39+I51");
        setFormula("K27", "IF(ISERROR((K39*L39+L51*K51)/L27),0,((K39*L39+L51*K51)/L27))");
        setFormula("L27", "L39+L51");

        // MgO(%) 子行
        setFormula("D28", "IF(ISERROR((D40*E39+E51*D52)/E27),0,((D40*E39+E51*D52)/E27))");
        setFormula("F28", "IF(ISERROR((E27*D28+K28*L27)/G27),0,(E27*D28+K28*L27)/G27)"); // 补充 F28
        setFormula("G28", "IF(ISERROR((E27*D28+K28*L27)/G27),0,(E27*D28+K28*L27)/G27)");
        setFormula("H28", "IF(ISERROR((H40*I39+I51*H52)/I27),0,((H40*I39+I51*H52)/I27))");
        setFormula("K28", "IF(ISERROR((K40*L39+L51*K52)/L27),0,((K40*L39+L51*K52)/L27))");

        // R2O3(%) 子行
        setFormula("D29", "IF(ISERROR((D41*E39+E51*D53)/E27),0,((D41*E39+E51*D53)/E27))");
        setFormula("F29", "IF(ISERROR((E27*D29+K29*L27)/G27),0,(E27*D29+K29*L27)/G27)"); // 补充 F29
        setFormula("G29", "IF(ISERROR((E27*D29+K29*L27)/G27),0,(E27*D29+K29*L27)/G27)");
        setFormula("H29", "IF(ISERROR((H41*I39+I51*H53)/I27),0,((H41*I39+I51*H53)/I27))");
        setFormula("K29", "IF(ISERROR((K41*L39+L51*K53)/L27),0,((K41*L39+L51*K53)/L27))");

        // SiO2(%) 子行
        setFormula("D30", "IF(ISERROR((D42*E39+E51*D54)/E27),0,((D42*E39+E51*D54)/E27))");
        setFormula("F30", "IF(ISERROR((E27*D30+K30*L27)/G27),0,(E27*D30+K30*L27)/G27)"); // 补充 F30
        setFormula("G30", "IF(ISERROR((E27*D30+K30*L27)/G27),0,(E27*D30+K30*L27)/G27)");
        setFormula("H30", "IF(ISERROR((H42*I39+I51*H54)/I27),0,((H42*I39+I51*H54)/I27))");
        setFormula("K30", "IF(ISERROR((K42*L39+L51*K54)/L27),0,((K42*L39+L51*K54)/L27))");
    }

    private void setupExternalOreFormulas() {
        // 2 外部矿石利用 - 完整公式
        setFormula("D31", "IF(ISERROR((D43*E43+E55*D55)/E31),0,((D43*E43+E55*D55)/E31))");
        setFormula("E31", "E43+E55");
        setFormula("F31", "(E31*D31+K31*L31)/G31"); // 补充 F31
        setFormula("G31", "(E31*D31+K31*L31)/G31");
        setFormula("H31", "IF(ISERROR((H43*I43+I55*H55)/I31),0,((H43*I43+I55*H55)/I31))");
        setFormula("I31", "I43+I55");
        setFormula("K31", "IF(ISERROR((K43*L43+L55*K55)/L31),0,((K43*L43+L55*K55)/L31))");
        setFormula("L31", "L43+L55");

        // MgO(%) 子行
        setFormula("D32", "IF(ISERROR((D44*E43+E55*D56)/E31),0,((D44*E43+E55*D56)/E31))");
        setFormula("F32", "(E31*D32+K32*L31)/G31"); // 补充 F32
        setFormula("G32", "(E31*D32+K32*L31)/G31");
        setFormula("H32", "IF(ISERROR((H44*I43+I55*H56)/I31),0,((H44*I43+I55*H56)/I31))");
        setFormula("K32", "IF(ISERROR((K44*L43+L55*K56)/L31),0,((K44*L43+L55*K56)/L31))");

        // R2O3(%) 子行
        setFormula("D33", "IF(ISERROR((D45*E43+E55*D57)/E31),0,((D45*E43+E55*D57)/E31))");
        setFormula("F33", "(E31*D33+K33*L31)/G31"); // 补充 F33
        setFormula("G33", "(E31*D33+K33*L31)/G31");
        setFormula("H33", "IF(ISERROR((H45*I43+I55*H57)/I31),0,((H45*I43+I55*H57)/I31))");
        setFormula("K33", "IF(ISERROR((K45*L43+L55*K57)/L31),0,((K45*L43+L55*K57)/L31))");

        // SiO2(%) 子行
        setFormula("D34", "IF(ISERROR((D46*E43+E55*D58)/E31),0,((D46*E43+E55*D58)/E31))");
        setFormula("F34", "(E31*D34+K34*L31)/G31"); // 补充 F34
        setFormula("G34", "(E31*D34+K34*L31)/G31");
        setFormula("H34", "IF(ISERROR((H46*I43+I55*H58)/I31),0,((H46*I43+I55*H58)/I31))");
        setFormula("K34", "IF(ISERROR((K46*L43+L55*K58)/L31),0,((K46*L43+L55*K58)/L31))");
    }

    private void setupShuangshanpingFormulas() {
        // 1 双山坪公司 - 完整公式
        setFormula("D35", "IF(ISERROR((D39*E39+D43*E43)/E35),0,((D39*E39+D43*E43)/E35))");
        setFormula("E35", "E39+E43");
        setFormula("F35", "(E35*D35+K35*L35)/G35"); // 补充 F35
        setFormula("G35", "(E35*D35+K35*L35)/G35");
        setFormula("H35", "IF(ISERROR((H39*I39+H43*I43)/I35),0,((H39*I39+H43*I43)/I35))");
        setFormula("I35", "I39+I43");
        setFormula("K35", "IF(ISERROR((K39*L39+K43*L43)/L35),0,((K39*L39+K43*L43)/L35))");
        setFormula("L35", "L39+L43");

        // MgO(%) 子行
        setFormula("D36", "IF(ISERROR((D40*E39+D44*E43)/E35),0,((D40*E39+D44*E43)/E35))");
        setFormula("F36", "(E35*D36+K36*L35)/G35"); // 补充 F36
        setFormula("G36", "(E35*D36+K36*L35)/G35");
        setFormula("H36", "IF(ISERROR((H40*I39+H44*I43)/I35),0,((H40*I39+H44*I43)/I35))");
        setFormula("K36", "IF(ISERROR((K40*L39+K44*L43)/L35),0,((K40*L39+K44*L43)/L35))");

        // R2O3(%) 子行
        setFormula("D37", "IF(ISERROR((D41*E39+D45*E43)/E35),0,((D41*E39+D45*E43)/E35))");
        setFormula("F37", "(E35*D37+K37*L35)/G35"); // 补充 F37
        setFormula("G37", "(E35*D37+K37*L35)/G35");
        setFormula("H37", "IF(ISERROR((H41*I39+H45*I43)/I35),0,((H41*I39+H45*I43)/I35))");
        setFormula("K37", "IF(ISERROR((K41*L39+K45*L43)/L35),0,((K41*L39+K45*L43)/L35))");

        // SiO2(%) 子行
        setFormula("D38", "IF(ISERROR((D42*E39+D46*E43)/E35),0,((D42*E39+D46*E43)/E35))");
        setFormula("F38", "(E35*D38+K38*L35)/G35"); // 补充 F38
        setFormula("G38", "(E35*D38+K38*L35)/G35");
        setFormula("H38", "IF(ISERROR((H42*I39+H46*I43)/I35),0,((H42*I39+H46*I43)/I35))");
        setFormula("K38", "IF(ISERROR((K42*L39+K46*L43)/L35),0,((K42*L39+K46*L43)/L35))");
    }

    private void setupShuangshanpingSelfProducedFormulas() {
        // 1.1 自产矿石（双山坪公司） - 完整公式
        setFormula("F39", "IF(ISERROR((E39*D39+K39*L39)/G39),0,((E39*D39+K39*L39)/G39))"); // 补充 F39
        setFormula("G39", "IF(ISERROR((E39*D39+K39*L39)/G39),0,((E39*D39+K39*L39)/G39))");
        setFormula("F40", "IF(ISERROR((E39*D40+K40*L39)/G39),0,((E39*D40+K40*L39)/G39))"); // 补充 F40
        setFormula("G40", "IF(ISERROR((E39*D40+K40*L39)/G39),0,((E39*D40+K40*L39)/G39))");
        setFormula("F41", "IF(ISERROR((E41*D41+K41*L41)/G41),0,((E41*D41+K41*L41)/G41))"); // 补充 F41
        setFormula("G41", "IF(ISERROR((E41*D41+K41*L41)/G41),0,((E41*D41+K41*L41)/G41))");
        setFormula("F42", "IF(ISERROR((E42*D42+K42*L42)/G42),0,((E42*D42+K42*L42)/G42))"); // 补充 F42
        setFormula("G42", "IF(ISERROR((E42*D42+K42*L42)/G42),0,((E42*D42+K42*L42)/G42))");
    }

    private void setupShuangshanpingExternalOreFormulas() {
        // 1.2 外部矿石利用（双山坪公司） - 完整公式
        setFormula("F43", "(E43*D43+K43*L43)/G43"); // 补充 F43
        setFormula("G43", "(E43*D43+K43*L43)/G43");
        setFormula("F44", "(E43*D44+K44*L43)/G43"); // 补充 F44
        setFormula("G44", "(E43*D44+K44*L43)/G43");
        setFormula("F45", "(E43*D45+K45*L43)/G43"); // 补充 F45
        setFormula("G45", "(E43*D45+K45*L43)/G43");
        setFormula("F46", "(E43*D46+K46*L43)/G43"); // 补充 F46
        setFormula("G46", "(E43*D46+K46*L43)/G43");
    }

    private void setupYoufuCompanyFormulas() {
        // 2 有福公司 - 完整公式
        setFormula("D47", "IF(ISERROR((D51*E51+D55*E55)/E47),0,((D51*E51+D55*E55)/E47))");
        setFormula("E47", "E51+E55");
        setFormula("F47", "IF(ISERROR((E47*D47+K47*L47)/G47),0,((E47*D47+K47*L47)/G47))"); // 补充 F47
        setFormula("G47", "IF(ISERROR((E47*D47+K47*L47)/G47),0,((E47*D47+K47*L47)/G47))");
        setFormula("H47", "IF(ISERROR((H51*I51+H55*I55)/I47),0,((H51*I51+H55*I55)/I47))");
        setFormula("I47", "I51+I55");
        setFormula("K47", "IF(ISERROR((K51*L51+K55*L55)/L47),0,((K51*L51+K55*L55)/L47))");
        setFormula("L47", "L51+L55");

        // MgO(%) 子行
        setFormula("D48", "IF(ISERROR((D52*E51+D56*E55)/E47),0,((D52*E51+D56*E55)/E47))");
        setFormula("F48", "IF(ISERROR((E47*D48+K48*L47)/G47),0,((E47*D48+K48*L47)/G47))"); // 补充 F48
        setFormula("G48", "IF(ISERROR((E47*D48+K48*L47)/G47),0,((E47*D48+K48*L47)/G47))");
        setFormula("H48", "IF(ISERROR((H52*I51+H56*I55)/I47),0,((H52*I51+H56*I55)/I47))");
        setFormula("K48", "IF(ISERROR((K52*L51+K56*L55)/L47),0,((K52*L51+K56*L55)/L47))");

        // R2O3(%) 子行
        setFormula("D49", "IF(ISERROR((D53*E51+D57*E55)/E47),0,((D53*E51+D57*E55)/E47))");
        setFormula("F49", "IF(ISERROR((E49*D49+K49*L47)/G49),0,((E49*D49+K49*L47)/G49))"); // 补充 F49
        setFormula("G49", "IF(ISERROR((E49*D49+K49*L47)/G49),0,((E49*D49+K49*L47)/G49))");
        setFormula("H49", "IF(ISERROR((H53*I51+H57*I55)/I47),0,((H53*I51+H57*I55)/I47))");
        setFormula("K49", "IF(ISERROR((K53*L51+K57*L55)/K47),0,((K53*L51+K57*L55)/L47))");

        // SiO2(%) 子行
        setFormula("D50", "IF(ISERROR((D54*E51+D58*E55)/E47),0,((D54*E51+D58*E55)/E47))");
        setFormula("F50", "IF(ISERROR((E50*D50+K50*L47)/G50),0,((E50*D50+K50*L47)/G50))"); // 补充 F50
        setFormula("G50", "IF(ISERROR((E50*D50+K50*L47)/G50),0,((E50*D50+K50*L47)/G50))");
        setFormula("H50", "IF(ISERROR((H54*I51+H58*I55)/I47),0,((H54*I51+H58*I55)/I47))");
        setFormula("K50", "IF(ISERROR((K54*L51+K58*L55)/L47),0,((K54*L51+K58*L55)/L47))");
    }

    private void setupYoufuSelfProducedFormulas() {
        // 2.1 自产矿石（有福公司） - 完整公式
        setFormula("F51", "IF(ISERROR((E51*D51+K51*L51)/G51),0,(E51*D51+K51*L51)/G51)"); // 补充 F51
        setFormula("G51", "IF(ISERROR((E51*D51+K51*L51)/G51),0,(E51*D51+K51*L51)/G51)");
        setFormula("F52", "IF(ISERROR((E51*D52+K52*L51)/G51),0,(E51*D52+K52*L51)/G51)"); // 补充 F52
        setFormula("G52", "IF(ISERROR((E51*D52+K52*L51)/G51),0,(E51*D52+K52*L51)/G51)");
        setFormula("F53", "IF(ISERROR((E51*D53+K53*L51)/G51),0,(E51*D53+K53*L51)/G51)"); // 补充 F53
        setFormula("G53", "IF(ISERROR((E51*D53+K53*L51)/G51),0,(E51*D53+K53*L51)/G51)");
        setFormula("F54", "IF(ISERROR((E51*D54+K54*L51)/G51),0,(E51*D54+K54*L51)/G51)"); // 补充 F54
        setFormula("G54", "IF(ISERROR((E51*D54+K54*L51)/G51),0,(E51*D54+K54*L51)/G51)");
    }

    private void setupYoufuExternalOreFormulas() {
        // 2.2 外部矿石利用（有福公司） - 完整公式
        setFormula("F55", "IF(ISERROR((E55*D55+K55*L55)/G55),0,((E55*D55+K55*L55)/G55))"); // 补充 F55
        setFormula("G55", "IF(ISERROR((E55*D55+K55*L55)/G55),0,((E55*D55+K55*L55)/G55))");
        setFormula("F56", "IF(ISERROR((E55*D56+K56*L55)/G55),0,((E55*D56+K56*L55)/G55))"); // 补充 F56
        setFormula("G56", "IF(ISERROR((E55*D56+K56*L55)/G55),0,((E55*D56+K56*L55)/G55))");
        setFormula("F57", "IF(ISERROR((E55*D57+K57*L55)/G55),0,((E55*D57+K57*L55)/G55))"); // 补充 F57
        setFormula("G57", "IF(ISERROR((E55*D57+K57*L55)/G55),0,((E55*D57+K57*L55)/G55))");
        setFormula("F58", "IF(ISERROR((E55*D58+K58*L55)/G55),0,((E55*D58+K58*L55)/G55))"); // 补充 F58
        setFormula("G58", "IF(ISERROR((E55*D58+K58*L55)/G55),0,((E55*D58+K58*L55)/G55))");
    }

    /**
     * 统一入口：先构建依赖图 → 拓扑排序 → 按顺序逐个计算公式。
     * 这样可以保证每个公式在其所有依赖已经计算完毕后才执行。
     */
    private void calculateAllFormulas() {
        log.info("开始构建公式依赖图并进行拓扑排序...");
        Map<String, Set<String>> dependGraph = buildDependGraph();
        List<String> orderedCells = topologicalOrder(dependGraph);
        log.info("拓扑排序完成，计算顺序长度 {}", orderedCells.size());

        for (String cell : orderedCells) {
            String formula = formulaMap.get(cell);
            if (formula != null) {
                calculateFormula(cell, formula);
            }
        }
        log.info("所有公式计算结束");
    }

    /**
     * 根据公式内容构建“被依赖 → 依赖它的目标”图。
     * 只记录引用本身也是公式的情况（即需要先计算的依赖）。
     */
    private Map<String, Set<String>> buildDependGraph() {
        // key：被依赖的单元格（ref），value：所有依赖该ref的目标单元格集合
        Map<String, Set<String>> graph = new HashMap<>();

        // 正则匹配单元格引用，如 D27、E31、AA7 等
        Pattern cellRefPattern = Pattern.compile("[A-Z]+[0-9]+");

        for (Map.Entry<String, String> entry : formulaMap.entrySet()) {
            String targetCell = entry.getKey();      // 需要计算的单元格
            String formula    = entry.getValue();   // 对应公式

            Matcher matcher = cellRefPattern.matcher(formula);
            while (matcher.find()) {
                String refCell = matcher.group();   // 公式里出现的引用

                // 只在引用本身也是公式时才建立依赖关系
                if (formulaMap.containsKey(refCell)) {
                    // refCell → targetCell
                    graph.computeIfAbsent(refCell, k -> new HashSet<>()).add(targetCell);
                }
            }
        }
        return graph;
    }

    /**
     * 对依赖图进行拓扑排序，返回安全的计算顺序。
     * 若检测到循环依赖，会在日志中提示并返回已排好的部分列表（后续仍会使用 0 兜底）。
     */
    private List<String> topologicalOrder(Map<String, Set<String>> graph) {
        // 1️⃣ 计算每个节点的入度（被多少公式依赖）
        Map<String, Integer> indegree = new HashMap<>();
        for (String cell : formulaMap.keySet()) {
            indegree.put(cell, 0);   // 初始化为 0
        }
        for (Set<String> outs : graph.values()) {
            for (String to : outs) {
                indegree.merge(to, 1, Integer::sum);
            }
        }

        // 2️⃣ 入度为 0 的节点进入队列
        Deque<String> queue = new ArrayDeque<>();
        for (Map.Entry<String, Integer> e : indegree.entrySet()) {
            if (e.getValue() == 0) {
                queue.add(e.getKey());
            }
        }

        // 3️⃣ Kahn 主循环
        List<String> order = new ArrayList<>();
        while (!queue.isEmpty()) {
            String cur = queue.poll();
            order.add(cur);

            // 删除 cur → out 的所有边，更新 out 的入度
            Set<String> outs = graph.getOrDefault(cur, Collections.emptySet());
            for (String out : outs) {
                indegree.merge(out, -1, Integer::sum);
                if (indegree.get(out) == 0) {
                    queue.add(out);
                }
            }
        }

        // 4️⃣ 循环检测
        if (order.size() != formulaMap.size()) {
            // 存在环路，业务上不应出现，记录日志供排查
            log.warn("公式存在循环依赖，已按部分拓扑顺序计算，剩余 {} 条公式将使用默认值 0",
                    formulaMap.size() - order.size());
            // 为了不影响后续执行，仍把未排进来的节点直接加入（顺序随意），后面会在计算时得到 0
            for (String cell : formulaMap.keySet()) {
                if (!order.contains(cell)) {
                    order.add(cell);
                }
            }
        }
        return order;
    }





    /**
     * 执行单个公式计算
     */
    private void calculateFormula(String targetCell, String formula) {
        try {
            // 预处理公式
            String processedFormula = preprocessFormula(formula);

            // 简化的公式计算逻辑
            double result = evaluateFormula(processedFormula);
            setCellValue(targetCell, result);

            log.debug("公式计算成功: {} = {} -> {}", targetCell, formula, result);
        } catch (Exception e) {
            log.warn("公式计算失败: {} = {}, 错误: {}", targetCell, formula, e.getMessage());
            setCellValue(targetCell, 0.0);
        }
    }

    /**
     * 简化的公式计算实现
     * 基于内存计算，支持基本的算术运算和IFERROR函数
     */
    private double evaluateFormula(String formula) {
        try {
            if (formula == null || formula.trim().isEmpty()) {
                return 0.0;
            }

            // 处理 IFERROR 函数
            if (formula.startsWith("IFERROR(")) {
                return evaluateIfErrorFormula(formula);
            }

            // 处理基本的算术表达式
            return evaluateArithmeticExpression(formula);

        } catch (Exception e) {
            log.warn("公式计算异常: {}, 错误: {}, 使用默认值0", formula, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 处理 IFERROR 函数
     * 格式: IFERROR(expression, fallback_value)
     */
    private double evaluateIfErrorFormula(String formula) {
        try {
            // 提取 IFERROR 内部的表达式
            String innerContent = formula.substring(8, formula.length() - 1); // 去掉 "IFERROR(" 和 ")"

            // 分割表达式和回退值
            int commaIndex = findOuterComma(innerContent);
            if (commaIndex == -1) {
                log.warn("IFERROR 公式格式错误: {}", formula);
                return 0.0;
            }

            String expression = innerContent.substring(0, commaIndex).trim();
            String fallbackStr = innerContent.substring(commaIndex + 1).trim();

            double fallbackValue = parseNumber(fallbackStr);

            try {
                // 尝试计算表达式
                return evaluateArithmeticExpression(expression);
            } catch (Exception e) {
                // 如果表达式计算失败，返回回退值
                return fallbackValue;
            }

        } catch (Exception e) {
            log.warn("IFERROR 公式解析失败: {}, 错误: {}", formula, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 计算算术表达式
     * 支持 +, -, *, /, 和单元格引用
     */
    private double evaluateArithmeticExpression(String expression) {
        try {
            // 替换所有单元格引用为实际值
            String resolvedExpression = resolveCellReferences(expression);

            // 计算表达式
            return evaluateSimpleExpression(resolvedExpression);

        } catch (Exception e) {
            log.warn("算术表达式计算失败: {}, 错误: {}", expression, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 解析表达式中的单元格引用
     */
    private String resolveCellReferences(String expression) {
        // 匹配单元格引用模式，如 D27, E23, K39 等
        Pattern cellRefPattern = Pattern.compile("[A-Z]+[0-9]+");
        Matcher matcher = cellRefPattern.matcher(expression);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            String cellRef = matcher.group();
            Object cellValue = cellValues.get(cellRef);

            if (cellValue != null) {
                // 如果单元格有值，使用该值
                String replacement = String.valueOf(cellValue);
                matcher.appendReplacement(result, replacement);
            } else {
                // 如果单元格没有值，使用0
                matcher.appendReplacement(result, "0");
            }
        }
        matcher.appendTail(result);

        return result.toString();
    }

    /**
     * 计算简单的算术表达式
     * 支持 +, -, *, / 和括号
     */
    private double evaluateSimpleExpression(String expression) {
        try {
            // 移除所有空格
            String cleanExpr = expression.replaceAll("\\s+", "");

            // 处理括号表达式
            while (cleanExpr.contains("(")) {
                cleanExpr = evaluateParentheses(cleanExpr);
            }

            // 计算乘除法
            cleanExpr = evaluateMultiplicationAndDivision(cleanExpr);

            // 计算加减法
            return evaluateAdditionAndSubtraction(cleanExpr);

        } catch (Exception e) {
            throw new RuntimeException("表达式计算失败: " + expression, e);
        }
    }

    /**
     * 处理括号表达式
     */
    private String evaluateParentheses(String expression) {
        Pattern parenPattern = Pattern.compile("\\(([^()]+)\\)");
        Matcher matcher = parenPattern.matcher(expression);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            String innerExpr = matcher.group(1);
            double innerResult = evaluateSimpleExpression(innerExpr);
            matcher.appendReplacement(result, String.valueOf(innerResult));
        }
        matcher.appendTail(result);

        return result.toString();
    }

    /**
     * 计算乘除法
     */
    private String evaluateMultiplicationAndDivision(String expression) {
        Pattern mdPattern = Pattern.compile("([-+]?[0-9]*\\.?[0-9]+)([*/])([-+]?[0-9]*\\.?[0-9]+)");
        Matcher matcher = mdPattern.matcher(expression);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            double left = Double.parseDouble(matcher.group(1));
            String operator = matcher.group(2);
            double right = Double.parseDouble(matcher.group(3));

            double operationResult;
            if ("*".equals(operator)) {
                operationResult = left * right;
            } else if ("/".equals(operator)) {
                if (right == 0) {
                    operationResult = 0; // 除零保护
                } else {
                    operationResult = left / right;
                }
            } else {
                operationResult = 0;
            }

            matcher.appendReplacement(result, String.valueOf(operationResult));
        }
        matcher.appendTail(result);

        return result.toString();
    }

    /**
     * 计算加减法
     */
    private double evaluateAdditionAndSubtraction(String expression) {
        // 处理连续的 +- 符号
        String cleanExpr = expression.replaceAll("--", "+")
                .replaceAll("\\+-", "-")
                .replaceAll("-\\+", "-")
                .replaceAll("\\+\\+", "+");

        // 分割数字和运算符
        Pattern asPattern = Pattern.compile("([-+]?[0-9]*\\.?[0-9]+)");
        Matcher matcher = asPattern.matcher(cleanExpr);

        double result = 0.0;
        boolean firstNumber = true;

        while (matcher.find()) {
            double number = Double.parseDouble(matcher.group(1));
            if (firstNumber) {
                result = number;
                firstNumber = false;
            } else {
                // 这里简化处理，实际上应该根据运算符处理
                result += number;
            }
        }

        return result;
    }

    /**
     * 查找最外层的逗号（不在括号内的逗号）
     */
    private int findOuterComma(String str) {
        int bracketCount = 0;
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (c == '(') {
                bracketCount++;
            } else if (c == ')') {
                bracketCount--;
            } else if (c == ',' && bracketCount == 0) {
                return i;
            }
        }
        return -1;
    }

    /**
     * 解析数字字符串
     */
    private double parseNumber(String numberStr) {
        try {
            return Double.parseDouble(numberStr.trim());
        } catch (NumberFormatException e) {
            // 如果是单元格引用，尝试获取值
            Object cellValue = cellValues.get(numberStr.trim());
            if (cellValue instanceof Number) {
                return ((Number) cellValue).doubleValue();
            }
            return 0.0;
        }
    }

    /**
     * 测试公式计算的方法（用于调试）
     */
    public void testFormulaCalculation() {
        // 设置一些测试数据
        cellValues.put("D27", 10.0);
        cellValues.put("E27", 20.0);
        cellValues.put("D31", 5.0);
        cellValues.put("E31", 15.0);
        cellValues.put("E23", 30.0);

        // 测试公式
        String testFormula = "IFERROR((D27*E27+D31*E31)/E23,0)";
        double result = evaluateFormula(testFormula);

        log.info("公式测试: {} = {}", testFormula, result);
        // 期望结果: (10*20 + 5*15)/30 = (200 + 75)/30 = 275/30 = 9.1666...
    }

    /**
     * 预处理公式
     */
    private String preprocessFormula(String formula) {
        if (formula == null || formula.isEmpty()) {
            return "0";
        }

        // 替换 ISERROR 为 IFERROR
        String fixed = rewriteIsError(formula);
        // 将外部工作簿引用统一置为 0
        fixed = fixed.replaceAll("\\[\\d+\\][^!]+!", "0");

        return fixed;
    }

    /**
     * 将 IF(ISERROR(...),0,(...)) 形式改写为 IFERROR(...,0)
     */
    private String rewriteIsError(String formula) {
        Pattern p = Pattern.compile(
                "IF\\s*\\(\\s*ISERROR\\s*\\(([^\\)]+)\\)\\s*,\\s*0\\s*,\\s*\\(([^\\)]+)\\)\\s*\\)",
                Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String inner = m.group(1).trim();
            String replacement = "IFERROR(" + inner + ",0)";
            m.appendReplacement(sb, replacement);
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /**
     * 保存处理后的数据
     */
    private void saveProcessedData() {
        try {
            log.info("开始保存处理后的数据，共 {} 个数据项", cellValues.size());

            List<ConsignmentProcessingReportSaveReqVO> reportDataList = new ArrayList<>();


            // === 第23行：二、购进磷矿石 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row23 = new ConsignmentProcessingReportSaveReqVO();
            row23.setSerialNumber("二");
            row23.setProductName("购进磷矿石");
            row23.setGrade("P2O5(%)");
            row23.setMonthlyAverage(getCellValueAsBigDecimal("D23"));
            row23.setMonthlyQuantity(getCellValueAsBigDecimal("E23"));
            row23.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G23"));
            row23.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F23"));
            row23.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H23"));
            row23.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I23"));
            row23.setYearCumulativeAverage(getCellValueAsBigDecimal("K23"));
            row23.setYearCumulativeQuantity(getCellValueAsBigDecimal("L23"));
            reportDataList.add(row23);

            // === 第24行：二、购进磷矿石 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row24 = new ConsignmentProcessingReportSaveReqVO();
            row24.setSerialNumber("二");
            row24.setProductName("购进磷矿石");
            row24.setGrade("MgO(%)");
            row24.setMonthlyAverage(getCellValueAsBigDecimal("D24"));
            row24.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G24"));
            row24.setLastYearCumulativeAverage(getCellValueAsBigDecimal("I24"));
            row24.setYearCumulativeAverage(getCellValueAsBigDecimal("K24"));
            reportDataList.add(row24);

            // === 第25行：二、购进磷矿石 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row25 = new ConsignmentProcessingReportSaveReqVO();
            row25.setSerialNumber("二");
            row25.setProductName("购进磷矿石");
            row25.setGrade("R2O3(%)");
            row25.setMonthlyAverage(getCellValueAsBigDecimal("D25"));
            row25.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G25"));
            row25.setLastYearCumulativeAverage(getCellValueAsBigDecimal("I25"));
            row25.setYearCumulativeAverage(getCellValueAsBigDecimal("K25"));
            reportDataList.add(row25);

            // === 第26行：二、购进磷矿石 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row26 = new ConsignmentProcessingReportSaveReqVO();
            row26.setSerialNumber("二");
            row26.setProductName("购进磷矿石");
            row26.setGrade("SiO2(%)");
            row26.setMonthlyAverage(getCellValueAsBigDecimal("D26"));
            row26.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G26"));
            row26.setLastYearCumulativeAverage(getCellValueAsBigDecimal("I26"));
            row26.setYearCumulativeAverage(getCellValueAsBigDecimal("K26"));
            reportDataList.add(row26);

            // === 第27行：1 自产矿石 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row27 = new ConsignmentProcessingReportSaveReqVO();
            row27.setSerialNumber("1");
            row27.setProductName("自产矿石");
            row27.setGrade("P2O5(%)");
            row27.setMonthlyAverage(getCellValueAsBigDecimal("D27"));
            row27.setMonthlyQuantity(getCellValueAsBigDecimal("E27"));
            row27.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G27"));
            row27.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F27"));
            row27.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H27"));   // ← 请补充对应单元格
            row27.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I27")); // ← 请补充对应单元格
            row27.setYearCumulativeAverage(getCellValueAsBigDecimal("K27"));
            row27.setYearCumulativeQuantity(getCellValueAsBigDecimal("L27"));
            reportDataList.add(row27);

// === 第28行：1 自产矿石 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row28 = new ConsignmentProcessingReportSaveReqVO();
            row28.setSerialNumber("1");
            row28.setProductName("自产矿石");
            row28.setGrade("MgO(%)");
            row28.setMonthlyAverage(getCellValueAsBigDecimal("D28"));
            row28.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G28"));
            row28.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H28"));
            row28.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I28"));
            row28.setYearCumulativeAverage(getCellValueAsBigDecimal("K28"));
            reportDataList.add(row28);

// === 第29行：1 自产矿石 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row29 = new ConsignmentProcessingReportSaveReqVO();
            row29.setSerialNumber("1");
            row29.setProductName("自产矿石");
            row29.setGrade("R2O3(%)");
            row29.setMonthlyAverage(getCellValueAsBigDecimal("D29"));
            row29.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G29"));
            row29.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H29"));
            row29.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I29"));
            row29.setYearCumulativeAverage(getCellValueAsBigDecimal("K29"));
            reportDataList.add(row29);

// === 第30行：1 自产矿石 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row30 = new ConsignmentProcessingReportSaveReqVO();
            row30.setSerialNumber("1");
            row30.setProductName("自产矿石");
            row30.setGrade("SiO2(%)");
            row30.setMonthlyAverage(getCellValueAsBigDecimal("D30"));
            row30.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G30"));
            row30.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H30"));
            row30.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I30"));
            row30.setYearCumulativeAverage(getCellValueAsBigDecimal("K30"));
            reportDataList.add(row30);

// === 第31行：2 外部矿石利用 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row31 = new ConsignmentProcessingReportSaveReqVO();
            row31.setSerialNumber("2");
            row31.setProductName("外部矿石利用");
            row31.setGrade("P2O5(%)");
            row31.setMonthlyAverage(getCellValueAsBigDecimal("D31"));
            row31.setMonthlyQuantity(getCellValueAsBigDecimal("E31"));
            row31.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G31"));
            row31.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F31"));
            row31.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H31"));
            row31.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I31"));
            row31.setYearCumulativeAverage(getCellValueAsBigDecimal("K31"));
            row31.setYearCumulativeQuantity(getCellValueAsBigDecimal("L31"));
            reportDataList.add(row31);

// === 第32行：2 外部矿石利用 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row32 = new ConsignmentProcessingReportSaveReqVO();
            row32.setSerialNumber("2");
            row32.setProductName("外部矿石利用");
            row32.setGrade("MgO(%)");
            row32.setMonthlyAverage(getCellValueAsBigDecimal("D32"));
            row32.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G32"));
            row32.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H32"));
            row32.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I32"));
            row32.setYearCumulativeAverage(getCellValueAsBigDecimal("K32"));
            reportDataList.add(row32);

// === 第33行：2 外部矿石利用 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row33 = new ConsignmentProcessingReportSaveReqVO();
            row33.setSerialNumber("2");
            row33.setProductName("外部矿石利用");
            row33.setGrade("R2O3(%)");
            row33.setMonthlyAverage(getCellValueAsBigDecimal("D33"));
            row33.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G33"));
            row33.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H33"));
            row33.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I33"));
            row33.setYearCumulativeAverage(getCellValueAsBigDecimal("K33"));
            reportDataList.add(row33);

// === 第34行：2 外部矿石利用 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row34 = new ConsignmentProcessingReportSaveReqVO();
            row34.setSerialNumber("2");
            row34.setProductName("外部矿石利用");
            row34.setGrade("SiO2(%)");
            row34.setMonthlyAverage(getCellValueAsBigDecimal("D34"));
            row34.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G34"));
            row34.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H34"));
            row34.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I34"));
            row34.setYearCumulativeAverage(getCellValueAsBigDecimal("K34"));
            reportDataList.add(row34);

// === 第35行：1 双山坪公司 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row35 = new ConsignmentProcessingReportSaveReqVO();
            row35.setSerialNumber("1");
            row35.setProductName("双山坪公司");
            row35.setGrade("P2O5(%)");
            row35.setMonthlyAverage(getCellValueAsBigDecimal("D35"));
            row35.setMonthlyQuantity(getCellValueAsBigDecimal("E35"));
            row35.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G35"));
            row35.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F35"));
            row35.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H35"));
            row35.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I35"));
            row35.setYearCumulativeAverage(getCellValueAsBigDecimal("K35"));
            row35.setYearCumulativeQuantity(getCellValueAsBigDecimal("L35"));
            reportDataList.add(row35);

// === 第36行：1 双山坪公司 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row36 = new ConsignmentProcessingReportSaveReqVO();
            row36.setSerialNumber("1");
            row36.setProductName("双山坪公司");
            row36.setGrade("MgO(%)");
            row36.setMonthlyAverage(getCellValueAsBigDecimal("D36"));
            row36.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G36"));
            row36.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H36"));
            row36.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I36"));
            row36.setYearCumulativeAverage(getCellValueAsBigDecimal("K36"));
            reportDataList.add(row36);

// === 第37行：1 双山坪公司 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row37 = new ConsignmentProcessingReportSaveReqVO();
            row37.setSerialNumber("1");
            row37.setProductName("双山坪公司");
            row37.setGrade("R2O3(%)");
            row37.setMonthlyAverage(getCellValueAsBigDecimal("D37"));
            row37.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G37"));
            row37.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H37"));
            row37.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I37"));
            row37.setYearCumulativeAverage(getCellValueAsBigDecimal("K37"));
            reportDataList.add(row37);

// === 第38行：1 双山坪公司 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row38 = new ConsignmentProcessingReportSaveReqVO();
            row38.setSerialNumber("1");
            row38.setProductName("双山坪公司");
            row38.setGrade("SiO2(%)");
            row38.setMonthlyAverage(getCellValueAsBigDecimal("D38"));
            row38.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G38"));
            row38.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H38"));
            row38.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I38"));
            row38.setYearCumulativeAverage(getCellValueAsBigDecimal("K38"));
            reportDataList.add(row38);

// === 第39行：1.1 自产矿石 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row39 = new ConsignmentProcessingReportSaveReqVO();
            row39.setSerialNumber("1.1");
            row39.setProductName("自产矿石");
            row39.setGrade("P2O5(%)");
            row39.setMonthlyAverage(getCellValueAsBigDecimal("D39"));
            row39.setMonthlyQuantity(getCellValueAsBigDecimal("E39"));
            row39.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G39"));
            row39.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F39"));
            row39.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H39"));
            row39.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I39"));
            row39.setYearCumulativeAverage(getCellValueAsBigDecimal("K39"));
            row39.setYearCumulativeQuantity(getCellValueAsBigDecimal("L39"));
            reportDataList.add(row39);

// === 第40行：1.1 自产矿石 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row40 = new ConsignmentProcessingReportSaveReqVO();
            row40.setSerialNumber("1.1");
            row40.setProductName("自产矿石");
            row40.setGrade("MgO(%)");
            row40.setMonthlyAverage(getCellValueAsBigDecimal("D40"));
            row40.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G40"));
            row40.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H40"));
            row40.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I40"));
            row40.setYearCumulativeAverage(getCellValueAsBigDecimal("K40"));
            reportDataList.add(row40);

// === 第41行：1.1 自产矿石 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row41 = new ConsignmentProcessingReportSaveReqVO();
            row41.setSerialNumber("1.1");
            row41.setProductName("自产矿石");
            row41.setGrade("R2O3(%)");
            row41.setMonthlyAverage(getCellValueAsBigDecimal("D41"));
            row41.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G41"));
            row41.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H41"));
            row41.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I41"));
            row41.setYearCumulativeAverage(getCellValueAsBigDecimal("K41"));
            reportDataList.add(row41);

// === 第42行：1.1 自产矿石 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row42 = new ConsignmentProcessingReportSaveReqVO();
            row42.setSerialNumber("1.1");
            row42.setProductName("自产矿石");
            row42.setGrade("SiO2(%)");
            row42.setMonthlyAverage(getCellValueAsBigDecimal("D42"));
            row42.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G42"));
            row42.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H42"));
            row42.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I42"));
            row42.setYearCumulativeAverage(getCellValueAsBigDecimal("K42"));
            reportDataList.add(row42);

// === 第43行：1.2 外部矿石利用 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row43 = new ConsignmentProcessingReportSaveReqVO();
            row43.setSerialNumber("1.2");
            row43.setProductName("外部矿石利用");
            row43.setGrade("P2O5(%)");
            row43.setMonthlyAverage(getCellValueAsBigDecimal("D43"));
            row43.setMonthlyQuantity(getCellValueAsBigDecimal("E43"));
            row43.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G43"));
            row43.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F43"));
            row43.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H43"));
            row43.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I43"));
            row43.setYearCumulativeAverage(getCellValueAsBigDecimal("K43"));
            row43.setYearCumulativeQuantity(getCellValueAsBigDecimal("L43"));
            reportDataList.add(row43);

// === 第44行：1.2 外部矿石利用 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row44 = new ConsignmentProcessingReportSaveReqVO();
            row44.setSerialNumber("1.2");
            row44.setProductName("外部矿石利用");
            row44.setGrade("MgO(%)");
            row44.setMonthlyAverage(getCellValueAsBigDecimal("D44"));
            row44.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G44"));
            row44.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H44"));
            row44.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I44"));
            row44.setYearCumulativeAverage(getCellValueAsBigDecimal("K44"));
            reportDataList.add(row44);

// === 第45行：1.2 外部矿石利用 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row45 = new ConsignmentProcessingReportSaveReqVO();
            row45.setSerialNumber("1.2");
            row45.setProductName("外部矿石利用");
            row45.setGrade("R2O3(%)");
            row45.setMonthlyAverage(getCellValueAsBigDecimal("D45"));
            row45.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G45"));
            row45.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H45"));
            row45.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I45"));
            row45.setYearCumulativeAverage(getCellValueAsBigDecimal("K45"));
            reportDataList.add(row45);

            // === 第46行：1.2 外部矿石利用 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row46 = new ConsignmentProcessingReportSaveReqVO();
            row46.setSerialNumber("1.2");
            row46.setProductName("外部矿石利用");
            row46.setGrade("SiO2(%)");
            row46.setMonthlyAverage(getCellValueAsBigDecimal("D46"));
            row46.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G46"));
            row46.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H46"));
            row46.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I46"));
            row46.setYearCumulativeAverage(getCellValueAsBigDecimal("K46"));
            reportDataList.add(row46);

            // === 第47行：2 有福公司 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row47 = new ConsignmentProcessingReportSaveReqVO();
            row47.setSerialNumber("2");
            row47.setProductName("有福公司");
            row47.setGrade("P2O5(%)");
            row47.setMonthlyAverage(getCellValueAsBigDecimal("D47"));
            row47.setMonthlyQuantity(getCellValueAsBigDecimal("E47"));
            row47.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G47"));
            row47.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F47"));
            row47.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H47"));
            row47.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I47"));
            row47.setYearCumulativeAverage(getCellValueAsBigDecimal("K47"));
            row47.setYearCumulativeQuantity(getCellValueAsBigDecimal("L47"));
            reportDataList.add(row47);

            // === 第48行：2 有福公司 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row48 = new ConsignmentProcessingReportSaveReqVO();
            row48.setSerialNumber("2");
            row48.setProductName("有福公司");
            row48.setGrade("MgO(%)");
            row48.setMonthlyAverage(getCellValueAsBigDecimal("D48"));
            row48.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G48"));
            row48.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H48"));
            row48.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I48"));
            row48.setYearCumulativeAverage(getCellValueAsBigDecimal("K48"));
            reportDataList.add(row48);

            // === 第49行：2 有福公司 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row49 = new ConsignmentProcessingReportSaveReqVO();
            row49.setSerialNumber("2");
            row49.setProductName("有福公司");
            row49.setGrade("R2O3(%)");
            row49.setMonthlyAverage(getCellValueAsBigDecimal("D49"));
            row49.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G49"));
            row49.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H49"));
            row49.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I49"));
            row49.setYearCumulativeAverage(getCellValueAsBigDecimal("K49"));
            reportDataList.add(row49);

            // === 第50行：2 有福公司 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row50 = new ConsignmentProcessingReportSaveReqVO();
            row50.setSerialNumber("2");
            row50.setProductName("有福公司");
            row50.setGrade("SiO2(%)");
            row50.setMonthlyAverage(getCellValueAsBigDecimal("D50"));
            row50.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G50"));
            row50.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H50"));
            row50.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I50"));
            row50.setYearCumulativeAverage(getCellValueAsBigDecimal("K50"));
            reportDataList.add(row50);

            // === 第51行：2.1 自产矿石 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row51 = new ConsignmentProcessingReportSaveReqVO();
            row51.setSerialNumber("2.1");
            row51.setProductName("自产矿石");
            row51.setGrade("P2O5(%)");
            row51.setMonthlyAverage(getCellValueAsBigDecimal("D51"));
            row51.setMonthlyQuantity(getCellValueAsBigDecimal("E51"));
            row51.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G51"));
            row51.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F51"));
            row51.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H51"));
            row51.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I51"));
            row51.setYearCumulativeAverage(getCellValueAsBigDecimal("K51"));
            row51.setYearCumulativeQuantity(getCellValueAsBigDecimal("L51"));
            reportDataList.add(row51);

            // === 第52行：2.1 自产矿石 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row52 = new ConsignmentProcessingReportSaveReqVO();
            row52.setSerialNumber("2.1");
            row52.setProductName("自产矿石");
            row52.setGrade("MgO(%)");
            row52.setMonthlyAverage(getCellValueAsBigDecimal("D52"));
            row52.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G52"));
            row52.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H52"));
            row52.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I52"));
            row52.setYearCumulativeAverage(getCellValueAsBigDecimal("K52"));
            reportDataList.add(row52);

            // === 第53行：2.1 自产矿石 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row53 = new ConsignmentProcessingReportSaveReqVO();
            row53.setSerialNumber("2.1");
            row53.setProductName("自产矿石");
            row53.setGrade("R2O3(%)");
            row53.setMonthlyAverage(getCellValueAsBigDecimal("D53"));
            row53.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G53"));
            row53.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H53"));
            row53.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I53"));
            row53.setYearCumulativeAverage(getCellValueAsBigDecimal("K53"));
            reportDataList.add(row53);

            // === 第54行：2.1 自产矿石 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row54 = new ConsignmentProcessingReportSaveReqVO();
            row54.setSerialNumber("2.1");
            row54.setProductName("自产矿石");
            row54.setGrade("SiO2(%)");
            row54.setMonthlyAverage(getCellValueAsBigDecimal("D54"));
            row54.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G54"));
            row54.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H54"));
            row54.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I54"));
            row54.setYearCumulativeAverage(getCellValueAsBigDecimal("K54"));
            reportDataList.add(row54);

            // === 第55行：2.2 外部矿石利用 P2O5(%) ===
            ConsignmentProcessingReportSaveReqVO row55 = new ConsignmentProcessingReportSaveReqVO();
            row55.setSerialNumber("2.2");
            row55.setProductName("外部矿石利用");
            row55.setGrade("P2O5(%)");
            row55.setMonthlyAverage(getCellValueAsBigDecimal("D55"));
            row55.setMonthlyQuantity(getCellValueAsBigDecimal("E55"));
            row55.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G55"));
            row55.setYearlyCumulativeQuantity(getCellValueAsBigDecimal("F55"));
            row55.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H55"));
            row55.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I55"));
            row55.setYearCumulativeAverage(getCellValueAsBigDecimal("K55"));
            row55.setYearCumulativeQuantity(getCellValueAsBigDecimal("L55"));
            reportDataList.add(row55);

            // === 第56行：2.2 外部矿石利用 MgO(%) ===
            ConsignmentProcessingReportSaveReqVO row56 = new ConsignmentProcessingReportSaveReqVO();
            row56.setSerialNumber("2.2");
            row56.setProductName("外部矿石利用");
            row56.setGrade("MgO(%)");
            row56.setMonthlyAverage(getCellValueAsBigDecimal("D56"));
            row56.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G56"));
            row56.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H56"));
            row56.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I56"));
            row56.setYearCumulativeAverage(getCellValueAsBigDecimal("K56"));
            reportDataList.add(row56);

            // === 第57行：2.2 外部矿石利用 R2O3(%) ===
            ConsignmentProcessingReportSaveReqVO row57 = new ConsignmentProcessingReportSaveReqVO();
            row57.setSerialNumber("2.2");
            row57.setProductName("外部矿石利用");
            row57.setGrade("R2O3(%)");
            row57.setMonthlyAverage(getCellValueAsBigDecimal("D57"));
            row57.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G57"));
            row57.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H57"));
            row57.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I57"));
            row57.setYearCumulativeAverage(getCellValueAsBigDecimal("K57"));
            reportDataList.add(row57);

            // === 第58行：2.2 外部矿石利用 SiO2(%) ===
            ConsignmentProcessingReportSaveReqVO row58 = new ConsignmentProcessingReportSaveReqVO();
            row58.setSerialNumber("2.2");
            row58.setProductName("外部矿石利用");
            row58.setGrade("SiO2(%)");
            row58.setMonthlyAverage(getCellValueAsBigDecimal("D58"));
            row58.setYearlyCumulativeAverage(getCellValueAsBigDecimal("G58"));
            row58.setLastYearCumulativeAverage(getCellValueAsBigDecimal("H58"));
            row58.setLastYearCumulativeQuantity(getCellValueAsBigDecimal("I58"));
            row58.setYearCumulativeAverage(getCellValueAsBigDecimal("K58"));
            reportDataList.add(row58);



            // 调用Service保存数据
            log.info("数据保存完成，共保存 {} 条记录", reportDataList.size());

            consignmentProcessingReportService.batchSaveConsignmentProcessingReport(reportDataList);
            log.info("数据保存完成");

        } catch (Exception e) {
            log.error("数据保存失败", e);
            throw new RuntimeException("数据保存失败", e);
        }
    }

    private BigDecimal getCellValueAsBigDecimal(String cellAddress) {
        Object value = cellValues.get(cellAddress);
        if (value instanceof Number) {
            return BigDecimal.valueOf(((Number) value).doubleValue());
        } else if (value instanceof BigDecimal) {
            return (BigDecimal) value;
        } else if (value != null) {
            try {
                return new BigDecimal(value.toString());
            } catch (NumberFormatException e) {
                return BigDecimal.ZERO;
            }
        }
        return BigDecimal.ZERO;
    }

    // ==================== 工具方法 ====================

    /**
     * 设置单元格值（带四舍五入）
     */
    private void setCellValue(String cellAddress, Object value) {
        if (value instanceof Number) {
            double rounded = round2(value);
            cellValues.put(cellAddress, rounded);
        } else if (value instanceof BigDecimal) {
            double rounded = round2(value);
            cellValues.put(cellAddress, rounded);
        } else {
            cellValues.put(cellAddress, value);
        }
    }

    /**
     * 设置公式
     */
    private void setFormula(String cellAddress, String formula) {
        if (formula != null && !formula.isEmpty()) {
            formulaMap.put(cellAddress, formula);
        }
    }

    /**
     * 从数据Map中获取值
     */
    private BigDecimal getValueFromData(String key) {
        return data != null && data.containsKey(key) ? data.get(key) : BigDecimal.ZERO;
    }

    /**
     * 四舍五入工具方法
     */
    private double round2(Object value) {
        if (value == null) {
            return 0.0;
        }
        BigDecimal bd;
        if (value instanceof Number) {
            bd = new BigDecimal(((Number) value).doubleValue());
        } else if (value instanceof BigDecimal) {
            bd = (BigDecimal) value;
        } else {
            try {
                bd = new BigDecimal(value.toString());
            } catch (Exception e) {
                return 0.0;
            }
        }
        return bd.setScale(2, RoundingMode.HALF_UP).doubleValue();
    }

}
